﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Skyworth.CRM.DAL
{
    public class UserDAL : SuperDataBase,IUserDAL
    {
        public void Add(Users model,List<int> roles)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into T_SYS_Users(");
                strSql.Append("IndividualId,UserName,Password,Email,UserType,LastLoginTime)");
                strSql.Append(" values (");
                strSql.Append("@IndividualId,@UserName,@Password,@Email,@UserType,getdate());select @NewID=@@IDENTITY");
                using (TransactionScope ts = new TransactionScope())
                {
                    DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                    DB.AddInParameter(cmd, "@IndividualId", DbType.Int32, model.IndividualId);
                    DB.AddInParameter(cmd, "@UserName", DbType.String, model.UserName);
                    DB.AddInParameter(cmd, "@Password", DbType.String, model.Password);
                    DB.AddInParameter(cmd, "@Email", DbType.String, model.Email);
                    DB.AddInParameter(cmd, "@UserType", DbType.Int32, model.UserType);
                    DB.AddOutParameter(cmd, "@NewID", DbType.Int32, 32);
                    DB.ExecuteNonQuery(cmd);
                    int newId = Convert.ToInt32(DB.GetParameterValue(cmd, "@NewID"));
                    string sql = "insert into [T_SYS_UserRoles]([UserID],[RoleID]) values(@UserID,@RoleID)";
                    for (int i = 0; i < roles.Count; i++)
                    {
                        cmd = DB.GetSqlStringCommand(sql.ToString());
                        DB.AddInParameter(cmd, "@UserID", DbType.Int32, newId);
                        DB.AddInParameter(cmd, "@RoleID", DbType.Int32, roles[i]);
                        DB.ExecuteNonQuery(cmd);
                    }
                    ts.Complete();
                }                
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(Users model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_SYS_Users set ");
                strSql.Append("IndividualId=@IndividualId,");
                strSql.Append("UserName=@UserName,");
                strSql.Append("Password=@Password,");
                strSql.Append("Email=@Email,");
                strSql.Append("UserType=@UserType,");
                strSql.Append("LastLoginTime=@LastLoginTime,");
                strSql.Append("IsDelete=@IsDelete,");
                strSql.Append("LastModifyTime=getdate()");
                strSql.Append(" where UserID=@UserID");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@UserID", DbType.Int32, model.UserId);
                DB.AddInParameter(cmd, "@IndividualId", DbType.Int32, model.IndividualId);
                DB.AddInParameter(cmd, "@UserName", DbType.String, model.UserName);
                DB.AddInParameter(cmd, "@Password", DbType.String, model.Password);
                DB.AddInParameter(cmd, "@Email", DbType.String, model.Email);
                DB.AddInParameter(cmd, "@UserType", DbType.Int32, model.UserType);
                DB.AddInParameter(cmd, "@LastLoginTime", DbType.DateTime, model.LastLoginTime);
                DB.AddInParameter(cmd, "@IsDelete", DbType.Boolean, model.IsDelete);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(Users model, List<int> roles)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_SYS_Users set ");
                strSql.Append("IndividualId=@IndividualId,");
                strSql.Append("UserName=@UserName,");
                strSql.Append("Password=@Password,");
                strSql.Append("Email=@Email,");
                strSql.Append("UserType=@UserType,");
                strSql.Append("LastLoginTime=@LastLoginTime,");
                strSql.Append("IsDelete=@IsDelete,");
                strSql.Append("LastModifyTime=getdate()");
                strSql.Append(" where UserID=@UserID");
                using (TransactionScope ts = new TransactionScope())
                {
                    DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                    DB.AddInParameter(cmd, "@UserID", DbType.Int32, model.UserId);
                    DB.AddInParameter(cmd, "@IndividualId", DbType.Int32, model.IndividualId);
                    DB.AddInParameter(cmd, "@UserName", DbType.String, model.UserName);
                    DB.AddInParameter(cmd, "@Password", DbType.String, model.Password);
                    DB.AddInParameter(cmd, "@Email", DbType.String, model.Email);
                    DB.AddInParameter(cmd, "@UserType", DbType.Int32, model.UserType);
                    DB.AddInParameter(cmd, "@LastLoginTime", DbType.DateTime, model.LastLoginTime);
                    DB.AddInParameter(cmd, "@IsDelete", DbType.Boolean, model.IsDelete);
                    DB.ExecuteNonQuery(cmd);
                    string sqlDel = "delete from [T_SYS_UserRoles] where [UserID] = @UserID";
                    cmd = DB.GetSqlStringCommand(sqlDel);
                    DB.AddInParameter(cmd, "@UserID", DbType.Int32, model.UserId);
                    DB.ExecuteNonQuery(cmd);
                    string sqlR = "insert into [T_SYS_UserRoles]([UserID],[RoleID]) values(@UserID,@RoleID)";
                    for (int i = 0; i < roles.Count; i++)
                    {
                        cmd = DB.GetSqlStringCommand(sqlR.ToString());
                        DB.AddInParameter(cmd, "@UserID", DbType.Int32, model.UserId);
                        DB.AddInParameter(cmd, "@RoleID", DbType.Int32, roles[i]);
                        DB.ExecuteNonQuery(cmd);
                    }
                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int userId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_SYS_Users set ");
                strSql.Append("IsDelete=1,");
                strSql.Append("LastModifyTime=getdate()");
                strSql.Append(" where UserID=@UserID");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@UserID", DbType.Int32, userId);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void SetUserAuthority(int modelId,IList<UserModules> list)
        { 
            string strSql = "insert into T_SYS_UserAuthority(UserID,ModuleID,FunctionID,Flag) values(@UserID,@ModuleID,@FunctionID,@Flag)";
            string sqlUser = "delete from T_SYS_UserAuthority where UserID = @UserID and (ModuleID=@ModuleID"
                +" or exists(select * from T_SYS_Modules as tsm where tsm.ParentModuleID = @ModuleID"
                + " and tsm.ModuleID = T_SYS_UserAuthority.ModuleID))";
            string sqlModel = "insert into T_SYS_UserAuthority(UserID,ModuleID,FunctionID,Flag) values(@UserID,@ModuleID,@FunctionID,@Flag)";
            try
            {
                if (list.Count > 0)
                {
                    using (TransactionScope ts = new TransactionScope())
                    {
                        DbCommand cmd = DB.GetSqlStringCommand(sqlUser);
                        DB.AddInParameter(cmd, "@UserID", DbType.Int32, list[0].UserID);
                        DB.AddInParameter(cmd, "@ModuleID", DbType.Int32, modelId);
                        DB.ExecuteNonQuery(cmd);
                        cmd = DB.GetSqlStringCommand(sqlModel);
                        DB.AddInParameter(cmd, "@UserID", DbType.Int32, list[0].UserID);
                        DB.AddInParameter(cmd, "@ModuleID", DbType.Int32, modelId);
                        DB.AddInParameter(cmd, "@FunctionID", DbType.Int32, 1); // 模块浏览功能
                        DB.AddInParameter(cmd, "@Flag", DbType.Boolean, true);
                        DB.ExecuteNonQuery(cmd);
                        for (int i = 0; i < list.Count; i++)
                        {
                            cmd = DB.GetSqlStringCommand(strSql);
                            DB.AddInParameter(cmd, "@UserID", DbType.Int32, list[i].UserID);
                            DB.AddInParameter(cmd, "@ModuleID", DbType.Int32, list[i].ModuleID);
                            DB.AddInParameter(cmd, "@FunctionID", DbType.Int32, list[i].FunctionID);
                            DB.AddInParameter(cmd, "@Flag", DbType.Boolean, list[i].Flag);
                            DB.ExecuteNonQuery(cmd);
                        }
                        ts.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Users GetModel(int userId)
        {
            try
            {
                Users model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 IndividualId,UserID,UserName,Password,Email,UserType,CreateTime,LastLoginTime,IsDelete,LastModifyTime from T_SYS_Users ");
                strSql.Append(" where UserID=@UserID");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@UserID", DbType.Int32, userId);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new Users();
                        if (dr["UserID"].ToString() != "")
                        {
                            model.UserId = int.Parse(dr["UserID"].ToString());
                        }
                        if (dr["IndividualId"].ToString() != "")
                        {
                            model.IndividualId = int.Parse(dr["IndividualId"].ToString());
                        }
                        model.UserName = dr["UserName"].ToString();
                        model.Password = dr["Password"].ToString();
                        model.Email = dr["Email"].ToString();
                        if (dr["UserType"].ToString() != "")
                        {
                            model.UserType = int.Parse(dr["UserType"].ToString());
                        }
                        if (dr["CreateTime"].ToString() != "")
                        {
                            model.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
                        }
                        if (dr["LastLoginTime"].ToString() != "")
                        {
                            model.LastLoginTime = DateTime.Parse(dr["LastLoginTime"].ToString());
                        }
                        if (dr["IsDelete"].ToString() != "")
                        {
                            if ((dr["IsDelete"].ToString() == "1") || (dr["IsDelete"].ToString().ToLower() == "true"))
                            {
                                model.IsDelete = true;
                            }
                            else
                            {
                                model.IsDelete = false;
                            }
                        }
                        if (dr["LastModifyTime"].ToString() != "")
                        {
                            model.LastModifyTime = DateTime.Parse(dr["LastModifyTime"].ToString());
                        }
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Exists(string userName, string password, out int userId,out int individualId,out int userType)
        {
                userId = -1;
                individualId = -1;
                userType = 1;//默认内部员工
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select top 1 UserID,IndividualId,UserType from T_SYS_Users where [IsDelete] = 0");
                strSql.Append("and (@UserName = '' or [UserName] = @UserName) and (@Password = '' or [Password] = @Password) ");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@UserName", DbType.String, userName);
                DB.AddInParameter(cmd, "@Password", DbType.String, password);
                DataTable dtUser  = DB.ExecuteDataSet(cmd).Tables[0];
                if ((Object.Equals(dtUser, null)) || (dtUser.Rows.Count<=0))
                {
                    return false;
                }
                else
                {
                    userId = int.Parse(dtUser.Rows[0]["UserID"].ToString());
                    individualId = int.Parse(dtUser.Rows[0]["IndividualId"].ToString());
                    userType = int.Parse(dtUser.Rows[0]["UserType"].ToString());
                    return true;
                }
           
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                string sql = @"SELECT distinct tsu.*,dbo.fn_GetRolesId(tsu.UserID) as roleid,dbo.fn_GetRolesName(tsu.UserID) as rolename,vbu.[NewName],
case UserType when 0 then '管理员' when 1 then '内部员工' else '客户' end as TypeName , c.BillNum 
FROM [T_SYS_Users] as tsu left join [V_BaseDabe_Users] as vbu on tsu.IndividualId=vbu.[NewCode] 
and tsu.UserType=vbu.[NewType] left join (select Fupdater,count(Fitemid) as BillNum from T_Order group by Fupdater) c on c.Fupdater=tsu.IndividualId where [IsDelete] = 0";
                sql += strWhere;
                sql += " order by tsu.UserID desc";
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, sql);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}